<!doctype html>
<html>
<head>
    <meta charset="utf-8">
    <title>mysql-explain使用</title>
</head>
<body>
<link href="css/style.css" rel="stylesheet">
<link href="css/app.css" rel="stylesheet">

<p>下面测试各种SQL，mysql explain执行结果。<a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html">mysql官方文档 EXPLAIN Output Format</a></p>
<p>表t1包含<mysql-count table="t1"></mysql-count>条数据, 表t2包含<mysql-count table="t2"></mysql-count>条数据</p>
<p>mysql版本: <mysql-version></mysql-version></p>

<div class="card">
    <div class="card-block">
        <mysql-explain sql="select * from t1" desc="全表扫描"></mysql-explain>
    </div>
</div>

<div class="card">
    <div class="card-block">
        <mysql-explain sql="select * from t1 where id = 1" desc="主键查询"></mysql-explain>
    </div>
</div>

<div class="card">
    <div class="card-block">
        <mysql-explain sql="select id from t1" desc="使用index_name索引，而不是主键索引"></mysql-explain>
    </div>
</div>

<div class="card">
    <div class="card-block">
        <mysql-explain sql="select * from t1 where 1 <= id and id <= 10" desc="主键范围查询"></mysql-explain>
    </div>
</div>

<div class="card">
    <div class="card-block">
        <mysql-explain sql="select name from t1 where name like '%a1%'" desc="只查询名称，字符串包含过滤，使用索引"></mysql-explain>
    </div>
</div>

<div class="card">
    <div class="card-block">
        <mysql-explain sql="select * from t1 where name like 'a1%'" desc="前缀匹配，使用索引"></mysql-explain>
        <br />
        <mysql-explain sql="select * from t1 where name like '%a1'" desc="后缀匹配，不会使用索引"></mysql-explain>
    </div>
</div>

<div class="card">
    <div class="card-block">
        <mysql-explain sql="select * from t1 where name <= 'b'" desc="mysql不够智能：这种情况，不使用索引，应该会好点"></mysql-explain>
    </div>
</div>

<div class="card">
    <div class="card-block">
        <mysql-explain sql="select * from t1 where name like '%a1' limit 1" desc="使用了limit 1，也没使用index_name索引"></mysql-explain>
    </div>
</div>

<div class="card">
    <div class="card-block">
        <mysql-explain sql="select * from t1, t2 where t1.id = t2.id" desc="主键关联"></mysql-explain>
    </div>
    <br />
    <div class="card-block">
        <mysql-explain sql="select * from t2, t1 where t1.id = t2.id" desc="这两条SQL，都是先查询表t2，再查询表t1"></mysql-explain>
    </div>
</div>

<div class="card">
    <div class="card-block">
        <mysql-explain sql="select * from t1 where id in (select id from t2 where name = 'a1' or name = 'a2') " desc="子查询"></mysql-explain>
    </div>
</div>

<div class="card">
    <div class="card-block">
        <mysql-explain sql="select * from t1 order by id desc limit 10" desc="主键排序，使用主键索引"></mysql-explain>
    </div>
</div>

<div class="card">
    <div class="card-block">
        <mysql-explain sql="select * from t1 order by name limit 1" desc="排序，使用limit 1，使用了索引。所以添加limit 1，有时挺有用"></mysql-explain>
        <br />
        <mysql-explain sql="select * from t1 order by name limit 4" desc="排序，使用limit 4，没有使用索引"></mysql-explain>
    </div>
</div>

<div class="card">
    <div class="card-block">
        <mysql-explain sql="select count(*) from t1 group by id" desc="按主键group by，使用了主键索引"></mysql-explain>
        <br />
        <mysql-explain sql="select count(*) from t1 group by id, name" desc="mysql不够智能：Using index; Using temporary; Using filesort"></mysql-explain>
    </div>
</div>

<script src="./js/riot.js"></script>
<script src="./js/jquery-2.1.4.js"></script>
<script src="./mysql.tag" type="riot/tag"></script>
<script>
    riot.mount('*');
</script>

</body>
</html>
